import pandas as pd
import pymysql, json
from itertools import groupby
import datetime
from dateutil.relativedelta import relativedelta
import re

import requests
from utilsapp.utils import ok,fail


# 插入，可批量
def db_insert(tableName, adddataList, cur):
    try:
        dataList = [adddataList] if type(adddataList) == dict else adddataList
        add_models = []
        not_db_keys = []
        for item in dataList:
            model = {key: item[key] for key in item if key not in not_db_keys}
            add_models.append({k: model[k] for k in sorted(model)})

        columns = '`, `'.join(add_models[0].keys())
        values = ', '.join(['%s'] * len(add_models[0]))
        sql = f"INSERT INTO `{tableName}` (`{columns}`) VALUES ({values})"
        params = [tuple(row.values()) for row in add_models]
        cur.executemany(sql, params)
        lastrowid = cur.lastrowid
        return {"Affected_rows": cur.rowcount, "lastrowid": lastrowid, 'msg': '新增成功', 'code': 0, 'success': True, }
    except Exception as e:
        return {"Affected_rows": 0, 'error': str(e), 'msg': '新增失败', 'code': -1, 'success': False, }


def main(dbconfig, excel_name, is_create_table):
    df = pd.read_excel(excel_name, skiprows=1, dtype={"终端号": str})

    row_count = len(df)
    if row_count <= 0:
        print('文件暂无数据')
        return fail('文件暂无数据')

    conn = pymysql.connect(host=dbconfig['host'], user=dbconfig['user'], port=dbconfig['port'],
                           password=dbconfig['password'], db=dbconfig['dbname'], charset='utf8', connect_timeout=10,
                           read_timeout=10, write_timeout=10, autocommit=False)

    cur = conn.cursor()
    conn.begin()
    regs = get_regs(cur)
    try:
        sensors = []

        for i in range(row_count):
            row = df.loc[i]
            address_num = str(row["终端通道"])
            match = re.search(r'\d+', address_num)
            address_num = f'_{match.group()}' if match else ''

            sensors.append({
                "group": row['集团'],
                "company": row['公司'],
                "factory": row['工厂'],
                "zone": row['区域'],
                "device_name": row['设备名称'],
                "device_code": row["设备编号"] if str(row["设备编号"]) != "nan" else None,
                "device_type":str(row['设备类型']).split('.')[0],
                "sensor_name": row["测点名称"],
                "direction": row["安装方向"],
                "sensor_type": str( row['终端类型']).split('.')[0],
                "address": str(row["终端号"]) + address_num,
                "port": row["服务器端口"],
                "fs": '12800',
            })
            if '网关MAC地址' in row and str(row["网关MAC地址"]) != "nan":
                try:
                    token = json.loads(requests.post('http://localhost:18083/api/v5/login', data=json.dumps({
                        'username': 'admin',
                        'password': 'emqx!zykc123',
                        }), headers={
                        'Content-Type': 'application/json',
                    }).text)['token']
                    res = requests.post('http://localhost:18083/api/v5/authentication/password_based:built_in_database/users', data=json.dumps({
                    'password': '123456',
                    'user_id': row['网关MAC地址'] + '-' + row['终端号'],
                    'is_superuser': False,
                    }),  headers={
                        'Content-Type': 'application/json',
                        'Authorization': f'bearer {token}'
                    })
                    res = requests.post('http://localhost:18083/api/v5/authentication/password_based:built_in_database/users', data=json.dumps({
                    'password': '123456',
                    'user_id': row['网关MAC地址'] + '-' + str(row['网关SN号']),
                    'is_superuser': False,
                    }),  headers={
                        'Content-Type': 'application/json',
                        'Authorization': f'bearer {token}'
                    })
                except Exception as e:
                    print('emqx账号创建失败: ' + str(e))
        frow = sensors[0]
        arr = ['group', 'company', 'factory']
        p = 0
        reg_ids = []
        for a in arr:
            t_n = next((t for t in regs if t['name'] == frow[a] and t['parent'] == p), None)
            if t_n is None:
                io= {"type": a, "parent": p, "name": frow[a]}
                if a=='group':
                    io['proj_no'] =get_proj(cur)
                p = db_insert('regionlevel', io, cur)["lastrowid"]
                # print(f'创建{a},{frow[a]},成功，{p}')
            else:
                p = t_n["id"]
                # print(f'跳过{a},{frow[a]},已存在，{p}')
            reg_ids.append(p)

        sensors_sort = sorted(sensors, key=lambda x: (x["zone"], x["device_name"]))
        sensors_gs = groupby(sensors_sort, key=lambda x: (x["zone"], x["device_name"]))

        zones = []
        for key, group in sensors_gs:
            gs = list(group)
            frow = gs[0]

            z = next((t for t in regs if t['name'] == key[0] and t['parent'] == reg_ids[2]), None)
            z = z if z is not None else next((t for t in zones if t['name'] == key[0]), None)
            if z is None:
                zone = db_insert('regionlevel', {"type": "zone", "parent": reg_ids[2], "name": key[0]}, cur)[
                    "lastrowid"]
                zones.append({"id": zone, "name": key[0]})
                # print(f'创建zone,{key[0]},成功，{zone}')
            else:
                zone = z['id']
            d = {"device_name": key[1],
                 "device_code": gs[0]["device_code"],
                 "type_id": frow['device_type'],
                 "group_id": reg_ids[0],
                 "status": 1,
                 "company_id": reg_ids[1],
                 "factory_id": reg_ids[2],
                 "zone_id": zone, }
            device_id = db_insert('device', d, cur)["lastrowid"]
            # print(f'创建设备成功,{key[1]},{device_id}')

            add_sensors = []
            for t in gs:
                add_sensors.append({
                    "group_id": reg_ids[0],
                    "company_id": reg_ids[1],
                    "factory_id": reg_ids[2],
                    "zone_id": zone,
                    "sensor_name": t["sensor_name"],
                    "direction": t["direction"],
                    "sensor_type": t['sensor_type'],
                    "address": t["address"],
                    "port": t["port"],
                    "fs": t["fs"],
                    "device_id": device_id
                })

            res = db_insert('sensor', add_sensors, cur)
            # print(key, res)
            if res['success'] == False:
                raise Exception(res['msg'])

        conn.commit()
        # if is_create_table:
        #     create_table(sensors, dbconfig)
        # print('导入成功')
        return ok(msg='导入成功')
    except Exception as e:
        conn.rollback()
        return fail('导入异常',err=e)



def get_regs(cur):
    cur.execute('SELECT type,`name`,id,parent FROM regionlevel ')
    datas = cur.fetchall()
    fields = [field[0] for field in cur.description]
    return [dict(zip(fields, result)) for result in datas]

def get_proj(cur):
    sql = "SELECT IFNULL(max(cast(proj_no as SIGNED)),0)+1 as no FROM regionlevel where type='group' and proj_no is not null"
    cur.execute(sql)
    num = cur.fetchone()[0]
    return f"{num:04d}"

def create_table(sensors, dbconfig):
    try:
        conn = pymysql.connect(host=dbconfig['host'], user=dbconfig['user'], port=dbconfig['port'],
                               password=dbconfig['password'], db='ieplat', charset='utf8', connect_timeout=10,
                               read_timeout=10, write_timeout=10, autocommit=False)

        cur = conn.cursor()
        conn.begin()

        try:
            now_time = datetime.datetime.now()

            cur.execute('SHOW TABLES;')
            datas = cur.fetchall()
            tbs = [t[0] for t in datas]

            for t in sensors:
                for i in range(2):
                    date = (now_time + relativedelta(months=i)).strftime('%Y%m')
                    name = f'_{t["port"]}_{t["address"]}_{date}'
                    if 'd' + name not in tbs:
                        cur.execute(get_sql('d', name))
                    if 's' + name not in tbs:
                        cur.execute(get_sql('s', name))
            conn.commit()
            # print('表创建成功')
        except Exception as e:
            print(e)
            conn.rollback()
    except Exception as e:
        print(e)


def drop_table(sensors, dbconfig):
    try:
        conn = pymysql.connect(host=dbconfig['host'], user=dbconfig['user'], port=dbconfig['port'],
                               password=dbconfig['password'], db='ieplat', charset='utf8', connect_timeout=10,
                               read_timeout=10, write_timeout=10, autocommit=False)

        cur = conn.cursor()
        conn.begin()

        try:
            cur.execute('SHOW TABLES;')
            datas = cur.fetchall()
            tbs = [t[0] for t in datas]
            for t in tbs:
                for s in sensors:
                    name = f'_{s["port"]}_{s["address"]}'
                    if name in t:
                        cur.execute(f'''DROP TABLE `{t}`''')
            conn.commit()
            print('表删除成功')
        except Exception as e:
            print(e)
            conn.rollback()
    except Exception as e:
        print(e)



def get_sql(p, name):
    return f'''
                CREATE TABLE `{p}{name}`  (
                  `id` int(11) NOT NULL AUTO_INCREMENT,
                  `feature_key` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '特征值key',
                  `value` mediumtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '点数据值',
                  `time` datetime NULL DEFAULT NULL COMMENT '时间戳',
                  PRIMARY KEY (`id`) USING BTREE,
                  INDEX `时间`(`time`) USING BTREE,
                  INDEX `类型`(`feature_key`) USING BTREE
                ) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
                '''

